How to Write a Common Table Expression

SQL Tutorial Home

A common table expression (also called CTE) is the result set of a select which is stored as an intermediate table, during the query execution. You can refer to the CTE name in the query as any other valid table. However after the query ends, the CTE is destroyed, and you can’t refer anymore.

WITH 
employee AS (
   SELECT 1 as emp_id, 'Smith' as last_name, 80000.00 as salary, 101 as dep_id
   UNION ALL
   SELECT 2, 'Sexton',  70000.00 , 101 UNION ALL
   SELECT 3, 'Maly',  90000.00 , 102 UNION ALL
   SELECT 4, 'Verstap', 125000.00 , 102
),
rows_and_position as 
  ( 
select emp_id,
last_name, 
salary,
dep_id,
row_number() over (partition by dep_id order by salary desc) as position
from employee
       )
select dep_id, last_name, salary
from  rows_and_position
where position = 1

In the previous query employee and rows_and_position are both CTEs and can be referenced as a regular tables in the rest of the query.

IN THIS PAGE